﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using NUnit.Framework;
using System.Data.Objects;
using System.Data.Objects.DataClasses;
using Northwind.NET.Model;
using Northwind.NET.Settings;
using Northwind.NET.BLL;


namespace Northwind.NET.Testing.Console
{
    [TestFixture]
    public class TestSet_0002_Reports
    {
        [SetUp]
        public void Setup() { }

        [TearDown]
        public void TearDown() { }

        [Test]
        public void SalesAnalysisReport()
        {
            NorthwindNETEntities _context = Globals.CreateObjectContext();

            //SimpleReportsDataSet_SalesAnalysisReport

            //sp_SalesAnalysisReport

            //SELECT Employee.Country, Employee.LastName, Employee.FirstName, 
            //YEAR(ShippedDate) AS ShippedYearNum, 
            //choose(MONTH(ShippedDate),1,1,1,2,2,2,3,3,3,4,4,4) AS QuarterNum,
            // MONTH(ShippedDate) AS ShippedMonthNum, 
            //[Order].ShippedDate, [Order].ID AS OrderID, [OrderSubtotals].Subtotal AS SaleAmount
            //FROM Employee, [Order], 
            //
            // [SELECT 
            //          [OrderDetail].OrderID, 
            //          Sum(CCur([UnitPrice]*[Quantity]*(1-[Discount])/100  
            //        )*100) AS Subtotal
            //        FROM [OrderDetail]
            //        GROUP BY [OrderDetail].OrderID
            //    ]. AS OrderSubTotals
            //
            //WHERE [Order].ID=[OrderSubtotals].OrderID and 
            //   Employee.ID=[Order].EmployeeID and
            //   not ([Order].ShippedDate is null)
            //ORDER BY Employee.LastName, Employee.FirstName, [Order].ShippedDate, [Order].ID;

            var OrderSubtotals = from d in _context.OrderDetail
                                     group d by d.OrderID into g
                                     select new
                                     {
                                         OrderID = g.Key,
                                         Subtotal = g.Sum(d => (float?)d.UnitPrice * d.Quantity * (1 - d.Discount))
                                     };
            var reportDataSet = from d in OrderSubtotals
                                join o in _context.Order on d.OrderID equals o.ID
                                join e in _context.Employee on o.EmployeeID equals e.ID
                                where (o.ShippedDate != null)
                                orderby e.LastName ascending, e.FirstName ascending,
                                        o.ShippedDate ascending, o.ID ascending
                                let monthNum = ((DateTime)o.ShippedDate).Month
                                select new
                                {
                                    e.Country, e.LastName, e.FirstName,
                                    ShippedYearNum = ((DateTime)o.ShippedDate).Year,
                                    QuarterNum = (monthNum >= 1 && monthNum <= 3) ? 1 :
                                                 (monthNum >= 4 && monthNum <= 6) ? 2 :
                                                 (monthNum >= 7 && monthNum <= 9) ? 3 :
                                                 (monthNum >= 10 && monthNum <= 12) ? 4 : -1,
                                    ShippedMonthNum = monthNum, 
                                    o.ShippedDate, OrderID = o.ID,  SaleAmount = d.Subtotal                                    
                                };

            foreach (var c in reportDataSet)
            {
                System.Console.WriteLine("{0} {1} {2} {3} {4} {5} {6} {7}",
                                    c.Country ,
                                    c.LastName,
                                    c.FirstName,
                                    c.QuarterNum,
                                    c.ShippedMonthNum, 
                                    c.ShippedDate,
                                    c.OrderID,
                                    c.SaleAmount  
                                    );
            }


            bool ret = true;
            Assert.IsTrue(ret, string.Format(Northwind.NET.Settings.Resources.TestFailedMessage,
                               System.Reflection.MethodInfo.GetCurrentMethod().Name));

        } 


        [Test]
        public void EmployeeSalesByCountryReport()
        {
            NorthwindNETEntities _context = Globals.CreateObjectContext();
        //SimpleReportsDataSet_EmployeeSalesByCountryReport

        //SELECT        
        //    Employee.Country, Employee.LastName, Employee.FirstName, 
        //    [Order].ShippedDate, [Order].ID AS OrderID, OrderTotals.OrderTotal
        //FROM            [Order] INNER JOIN
        //
        //      (SELECT        OrderID, 
        //                         SUM(CONVERT(money, (UnitPrice * Quantity) * (1 - Discount) / 100) * 100) 
        //       AS OrderTotal
        //      FROM            OrderDetail
        //     GROUP BY OrderID) AS OrderTotals 
        // 
        //   ON [Order].ID = OrderTotals.OrderID INNER JOIN
        //                         Employee ON [Order].EmployeeID = Employee.ID
        //WHERE        ([Order].ShippedDate BETWEEN @DateFrom AND @DateTo

            DateTime? DateFrom = null;
            DateTime? DateTo = null;

            var orderTotals = from d in _context.OrderDetail
                                     group d by d.OrderID into g
                                     select new
                                     {
                                         OrderID = g.Key,
                                         OrderTotal = g.Sum(d => (float?)d.UnitPrice * d.Quantity * (1 - d.Discount))
                                     };
            var reportDataSet = from o in _context.Order
                                 join d in orderTotals on o.ID equals d.OrderID
                                 join e in _context.Employee on o.EmployeeID equals e.ID
                                 where o.ShippedDate != null &&
                                       (DateFrom == null && DateTo == null) ||
                                       (o.ShippedDate >= DateFrom && o.ShippedDate <= DateTo)
                                 select new
                                 {
                                     e.Country, e.LastName, e.FirstName, 
                                     o.ShippedDate,  OrderID = o.ID, d.OrderTotal
                                 };

            foreach (var c in reportDataSet)
            {
                System.Console.WriteLine("{0} {1} {2} {3} {4} {5}",
                                    c.Country ,
                                    c.LastName,
                                    c.FirstName,
                                    c.ShippedDate,
                                    c.OrderID,
                                    c.OrderTotal 
                                    );
            }


            bool ret = true;
            Assert.IsTrue(ret, string.Format(Northwind.NET.Settings.Resources.TestFailedMessage,
                               System.Reflection.MethodInfo.GetCurrentMethod().Name));

        } 

        [Test]
        public void SalesByYearReport()
        {
            NorthwindEntities _context = Globals.CreateObjectContext();

            int? yearNumArg = null;

            //SimpleReportsDataSet_SalesByYear

            //SELECT [OrderDetail].OrderID, Sum(CCur([UnitPrice]*[Quantity]*(1-[Discount])/100)*100) AS Subtotal
            //FROM OrderDetail
            //GROUP BY [OrderDetail].OrderID;
            var udf_OrderSubtotals = from d in _context.OrderDetail
                                     group d by d.OrderID into g
                                     select new
                                     {
                                         OrderId = g.Key,
                                         SubTotal = g.Sum(d => (float?)d.UnitPrice * d.Quantity * (1 - d.Discount))
                                     };


            //PARAMETERS yearNum Long;
            //SELECT Order.ShippedDate, OrderSubtotals.OrderID, OrderSubtotals.Subtotal, 
            //Choose(Month(ShippedDate),1,1,1,2,2,2,3,3,3,4,4,4) AS QuarterNum, Year([ShippedDate]) AS [Year]
            //FROM [Order], [SELECT * FROM udf_OrderSubtotals]. AS OrderSubtotals
            //WHERE (((Order.ID)=[OrderSubtotals].[OrderID]) AND ((Year([Order].[ShippedDate]))=[yearNum]));

            var reportDataSet = from d in udf_OrderSubtotals
                                join o in _context.Order on d.OrderId equals o.ID
                                let yearNum = ((DateTime)o.ShippedDate).Year
                                let monthNum = ((DateTime)o.ShippedDate).Month
                                let quarterNum = (monthNum >= 1 && monthNum <= 3) ? 1 :
                                                 (monthNum >= 4 && monthNum <= 6) ? 2 :
                                                 (monthNum >= 7 && monthNum <= 9) ? 3 :
                                                 (monthNum >= 10 && monthNum <= 12) ? 4 : -1
                                where (yearNumArg == null || yearNum == yearNumArg) &&
                                       o.ShippedDate != null
                                orderby yearNum ascending, quarterNum ascending, d.SubTotal descending  
                                select new
                                {
                                    o.ShippedDate,
                                    d.OrderId,
                                    d.SubTotal,
                                    QuarterNum = quarterNum,
                                    Year = yearNum
                                };


            foreach (var c in reportDataSet)
            {
                System.Console.WriteLine("{0} {1} {2} {3} {4}",
                                    c.ShippedDate, 
                                    c.OrderId, 
                                    c.SubTotal, 
                                    c.Year, 
                                    c.QuarterNum
                                    );
            }

        //  <DataField>ShippedDate</DataField>
        //  <rd:TypeName>System.DateTime</rd:TypeName>
        //</Field>
        //<Field Name="OrderID">
        //  <DataField>OrderID</DataField>
        //  <rd:TypeName>System.Int32</rd:TypeName>
        //</Field>
        //<Field Name="Subtotal">
        //  <DataField>Subtotal</DataField>
        //  <rd:TypeName>System.Decimal</rd:TypeName>
        //</Field>
        //<Field Name="Year">
        //  <DataField>Year</DataField>
        //  <rd:TypeName>System.Int32</rd:TypeName>
        //</Field>
        //<Field Name="QuarterNum">
        //  <DataField>QuarterNum</DataField>
        //  <rd:TypeName>System.Int32</rd:TypeName>


            bool ret = true;
            Assert.IsTrue(ret, string.Format(Northwind.NET.Settings.Resources.TestFailedMessage,
                               System.Reflection.MethodInfo.GetCurrentMethod().Name));

        } 

        [Test]
        public void SalesTotalsByAmountReport()
        {
            //SimpleReportsDataSet_SalesTotalsByAmountReport

            //SELECT [OrderSubTotals].Subtotal AS SaleAmount, 
            //[Order].ID, Customer.Name, [Order].ShippedDate, 
            //CLNG(Fix([OrderSubTotals].Subtotal/1000)) AS GroupValue
            //FROM Customer, [Order], [SELECT [OrderDetail].OrderID, 
            //Sum(CCur([UnitPrice]*[Quantity]*(1-[Discount])/100)*100) AS Subtotal 
            //FROM OrderDetail GROUP BY [OrderDetail].OrderID]. AS OrderSubTotals
            //WHERE Customer.ID=Order.CustomerID And Order.ID=OrderSubTotals.OrderId 
            //And (OrderSubtotals.Subtotal>2500) 
            //And (Order.ShippedDate Between #1/1/1997# And #12/31/1997#)
            //ORDER BY [OrderSubTotals].Subtotal DESC;

            NorthwindNETEntities _context = Globals.CreateObjectContext();

            int? yearNumArg = null;

            //SELECT     [Order].ID AS OrderID, Customer.Name AS CustomerName, [Order].OrderDate, YEAR([Order].OrderDate) AS OrderYear, OrderTotals.OrderTotal
            //FROM         Customer INNER JOIN
            //                      [Order] ON Customer.ID = [Order].CustomerId INNER JOIN
            // (SELECT     OrderID, SUM(CONVERT(money, (UnitPrice * Quantity) * (1 - Discount) / 100) * 100) AS OrderTotal
            //             FROM          OrderDetail
            //  GROUP BY OrderID) 
            // AS OrderTotals ON [Order].ID = OrderTotals.OrderID
            //WHERE     (YEAR([Order].OrderDate) = @OrderYear) AND (OrderTotals.OrderTotal &gt; @OrderTotal)

            var reportDataSet1 = from d in _context.OrderDetail
                           group d by d.OrderID into g
                           select new
                           {
                               OrderId = g.Key,
                               OrderTotal = g.Sum(r => (float?)r.UnitPrice * r.Quantity * (1 - r.Discount))
                           };
            var reportDataSet2 = from d in reportDataSet1
                           join o in _context.Order on d.OrderId equals o.ID
                           join c in _context.Customer on o.CustomerId equals c.ID 
                           let yearNum =  (o.OrderDate != null)? ((DateTime)o.OrderDate).Year: -1
                           where yearNumArg == null || yearNum == yearNumArg
                           orderby d.OrderTotal descending 
                           select new
                           {
                                SaleAmount = d.OrderTotal,
                                o.ID ,
                                c.Name, 
                                o.ShippedDate,
                                GroupValue = (int)d.OrderTotal/1000,
                                OrderYear = yearNum
                           };

            foreach (var c in reportDataSet2)
            {
                System.Console.WriteLine("{0} {1} {2} {3} {4} {5}",
                                    c.SaleAmount, //  OrderID,
                                    c.ID, // CustomerName,
                                    c.Name, // OrderDate,
                                    c.ShippedDate, //  OrderYear,
                                    c.GroupValue,
                                    c.OrderYear);
            }

            bool ret = true;
            Assert.IsTrue(ret, string.Format(Northwind.NET.Settings.Resources.TestFailedMessage,
                               System.Reflection.MethodInfo.GetCurrentMethod().Name));

        }        
        
        [Test]
        public void SalesByCategory()
        {
 
            //SimpleReportsDataSet_SalesByCategory

            //SELECT [OrderDetail].OrderID, [OrderDetail].ProductID, Product.Name AS ProductName, [OrderDetail].UnitPrice,
            //[OrderDetail].Quantity, [OrderDetail].Discount, CCur(([OrderDetail].UnitPrice*[Quantity]*(1-[Discount])/100)*100) AS ExtendedPrice
            //FROM Product, OrderDetail
            //WHERE Product.ID=OrderDetail.ProductID;


            //PARAMETERS categoryId Long;
            //SELECT Product.CategoryID, Category.Name AS CategoryName, Product.Name AS ProductName, 
            //Sum([OrderDetailsExtended].ExtendedPrice) AS ProductSales
            //FROM Category, Product, [Order], 
            //[SELECT * FROM udf_OrderDetailsExtended]. AS OrderDetailsExtended
            //WHERE Order.ID=OrderDetailsExtended.OrderID And Product.ID=OrderDetailsExtended.ProductID And
            // Category.ID=Product.CategoryID And (categoryId Is Null Or Category.ID=categoryId)
            //GROUP BY Product.CategoryID, Category.Name, Product.Name
            //ORDER BY Category.Name;

            int? categoryId = null;
            
            NorthwindNETEntities _context = Globals.CreateObjectContext();
            var udf_OrderDetailsExtended = from d in _context.OrderDetail
                                           join p in _context.Product
                                            on d.ProductID equals p.ID
                                           select new
                                           {
                                               d.OrderID,
                                               d.ProductID,
                                               ProductName = p.Name,
                                               UnitPrice = (float?)d.UnitPrice,
                                               d.Quantity,
                                               d.Discount,
                                               ExtendedPrice = (float?)d.UnitPrice * d.Quantity * (1 - d.Discount)
                                           };

            const string DELIMITER = "{***}";
            var reportDataSet1 = from r in udf_OrderDetailsExtended
                                 join o in _context.Order on r.OrderID equals o.ID
                                 join p in _context.Product on r.ProductID equals p.ID
                                 join c in _context.Category on p.CategoryID equals c.ID
                                 select new
                                 {
                                     GroupKey = c.Name + DELIMITER + p.Name, 
                                     p.CategoryID, 
                                     CategoryName = c.Name, 
                                     ProductName = p.Name,
                                     r.ExtendedPrice 
                                     //Sum([OrderDetailsExtended].ExtendedPrice) AS ProductSales
                                 };

            var reportDataSet2 = from r in reportDataSet1 
                                 group r by r.GroupKey into g
                                 select new
                                 {
                                     g.Key,
                                     ProductSales = g.Sum(r=>r.ExtendedPrice) 
                                 };

            var reportDataSet3 = from r in reportDataSet2
                                 let pos = r.Key.IndexOf(DELIMITER)
                                 let categoryName = r.Key.Substring(0, pos)
                                 let productName = r.Key.Substring(pos + DELIMITER.Length)
                                 join c in _context.Category on categoryName equals c.Name  
                                 select new
                                 {
                                     GroupKey = r.Key,
                                     CategoryId = c.ID,
                                     CategoryName = categoryName,
                                     ProductName = productName,                                
                                     r.ProductSales
                                 };


            foreach (var c in reportDataSet3)
            {
                System.Console.WriteLine("[{0}] {1} {2} {3} {4}",
                                    c.GroupKey,
                                    c.CategoryId, 
                                    c.CategoryName,
                                    c.ProductName,
                                    c.ProductSales);
            }

            bool ret = true;
            Assert.IsTrue(ret, string.Format(Northwind.NET.Settings.Resources.TestFailedMessage,
                               System.Reflection.MethodInfo.GetCurrentMethod().Name));

        }

        [Test]
        public void SummaryOfSalesByQuarterReport()
        {
            NorthwindNETEntities _context = Globals.CreateObjectContext();
            var reportDataSet1 = from d in _context.OrderDetail
                                 group d by d.OrderID into g
                                 select new
                                 {
                                     OrderId = g.Key,
                                     Subtotal = g.Sum(r => (float?)r.UnitPrice * r.Quantity * (1 - r.Discount))
                                 };
            var reportDataSet2 = from o in _context.Order
                                 join d in reportDataSet1 on o.ID equals d.OrderId
                                 where o.ShippedDate != null
                                 select new
                                 {
                                     o.ShippedDate,
                                     OrderId = o.ID,
                                     d.Subtotal
                                 };

            var reportDataSet3 = from r in reportDataSet2
                                 let yearNum = ((DateTime)r.ShippedDate).Year
                                 let monthNum = ((DateTime)r.ShippedDate).Month
                                 let quarterNum = (monthNum >= 1 && monthNum <= 3) ? 1 :
                                                  (monthNum >= 4 && monthNum <= 6) ? 2 :
                                                  (monthNum >= 7 && monthNum <= 9) ? 3 :
                                                  (monthNum >= 10 && monthNum <= 12) ? 4 : -1
                                 let groupKey = quarterNum*10000 + yearNum
                                 select new
                                 {
                                     GroupByKey = groupKey,
                                     YearNum = yearNum,
                                     MonthNum = monthNum,
                                     QuarterNum = quarterNum,
                                     r.OrderId,
                                     r.Subtotal
                                 };

            var reportDataSet4 = from r in reportDataSet3
                                 group r by r.GroupByKey into g
                                 select new
                                 {
                                     GroupKey = g.Key,
                                     OrdersCount = g.Count(r => r.OrderId != null),
                                     SalesAmount = g.Sum(r => r.Subtotal)
                                 };

            var reportDataSet5 = from r in reportDataSet4
                                 let yearNum = r.GroupKey - ((r.GroupKey / 10000)*10000)
                                 let quarterNum = r.GroupKey / 10000
                                 orderby r.GroupKey
                                 select new
                                 {
                                     r.GroupKey,
                                     YearNum = yearNum,
                                     QuarterNum = quarterNum,
                                     r.OrdersCount,
                                     r.SalesAmount
                                 };

            foreach (var c in reportDataSet5)
            {
                System.Console.WriteLine("{0} {1} {2} {3} {4}",
                                    c.GroupKey,
                                    c.YearNum,
                                    c.QuarterNum,
                                    c.OrdersCount,
                                    c.SalesAmount);
            }

            bool ret = true;
            Assert.IsTrue(ret, string.Format(Northwind.NET.Settings.Resources.TestFailedMessage,
                               System.Reflection.MethodInfo.GetCurrentMethod().Name));

        }
  

        [Test]
        public void SummaryOfSalesByYearReport() 
        {
            //SimpleReportsDataSet_SummaryOfSalesByYearReport

            //SELECT YearNum, [QuarterNum], Count(OrderId) AS OrdersCount, SUM(Subtotal) AS SalesAmount
            //FROM
            // [Select 
            //YEAR(ShippedDate) as [YearNum],
            //Choose(MONTH(ShippedDate),1,1,1,2,2,2,3,3,3,4,4,4)
            //as QuarterNum,
            //OrderId,
            //Subtotal 
            //from
            //(
            //    SELECT 
            //       [Order].ShippedDate, 
            //       [Order].ID as OrderID, 
            //       [OrderSubtotals].Subtotal
            //    FROM [Order] INNER JOIN
            //    (
            //        SELECT 
            //          [OrderDetail].OrderID, 
            //          Sum(CCur([UnitPrice]*[Quantity]*(1-[Discount])/100  
            //            )*100) AS Subtotal
            //        FROM [OrderDetail]
            //        GROUP BY [OrderDetail].OrderID
            //    ) OrderSubTotals 
            //    ON [Order].ID = [OrderSubtotals].OrderID
            //    WHERE ([Order].ShippedDate Is Not Null)
            //) r
            //]. AS r1
            //GROUP BY YearNum, [QuarterNum]
            //ORDER BY YearNum, [QuarterNum];

            NorthwindNETEntities _context = Globals.CreateObjectContext();
            var reportDataSet1 = from d in _context.OrderDetail
                                 group d by d.OrderID into g
                                 select new
                                 {
                                    OrderId = g.Key,
                                    Subtotal = g.Sum(r => (float?)r.UnitPrice*r.Quantity*(1-r.Discount))
                                 };
            var reportDataSet2 = from o in _context.Order
                                 join d in reportDataSet1 on o.ID equals d.OrderId
                                 where o.ShippedDate != null
                                 select new
                                 {
                                     o.ShippedDate,
                                     OrderId = o.ID,
                                     d.Subtotal
                                 };

            //[Select 
            //YEAR(ShippedDate) as [YearNum],
            //Choose(MONTH(ShippedDate),1,1,1,2,2,2,3,3,3,4,4,4)
            //as QuarterNum,
            //OrderId,
            //Subtotal 
            var reportDataSet3 = from r in reportDataSet2
                                 let yearNum = ((DateTime)r.ShippedDate).Year
                                 let monthNum = ((DateTime)r.ShippedDate).Month
                                 let quarterNum = (monthNum >= 1 && monthNum <= 3) ? 1 :
                                                  (monthNum >= 4 && monthNum <= 6) ? 2 :
                                                  (monthNum >= 7 && monthNum <= 9) ? 3 :
                                                  (monthNum >= 10 && monthNum <= 12) ? 4 : -1
                                 //let groupKey = yearNum * 1000 + quarterNum * 100 + monthNum
                                 let groupKey = yearNum * 10 + quarterNum 
                                 select new
                                 {
                                     GroupByKey = groupKey, 
                                     YearNum = yearNum,
                                     MonthNum = monthNum,
                                     QuarterNum =  quarterNum,
                                     r.OrderId,
                                     r.Subtotal
                                 };

            //SELECT YearNum, [QuarterNum], Count(OrderId) AS OrdersCount, SUM(Subtotal) AS SalesAmount
            var reportDataSet4 = from r in reportDataSet3
                                 group r by r.GroupByKey into g
                                 select new
                                 {
                                     GroupKey = g.Key,
                                     OrdersCount = g.Count(r => r.OrderId != null),
                                     SalesAmount = g.Sum(r => r.Subtotal)
                                 };

            //GROUP BY YearNum, [QuarterNum]
            //ORDER BY YearNum, [QuarterNum];

            var reportDataSet5 = from r in reportDataSet4
                                 let yearNum = r.GroupKey / 10 //00
                                 let quarterNum = r.GroupKey - yearNum * 10 //00
                                 //let monthNum = r.GroupKey - yearNum * 1000
                                 orderby r.GroupKey 
                                 select new
                                 {
                                     r.GroupKey,
                                     YearNum = yearNum,
                                     QuarterNum = quarterNum,
                                     //MonthNum = monthNum,
                                     r.OrdersCount,
                                     r.SalesAmount
                                 };

            foreach (var c in reportDataSet5)
            {
                //System.Console.WriteLine("{0} {1} {2} {3} {4} {5} {6} {7}",
                System.Console.WriteLine("{0} {1} {2} {3} {4}",
                                    c.GroupKey,
                                    c.YearNum,
                                    c.QuarterNum, 
                                    //c.MonthNum, 
                                    c.OrdersCount,
                                    c.SalesAmount);
            }

            bool ret = true;
            Assert.IsTrue(ret, string.Format(Northwind.NET.Settings.Resources.TestFailedMessage,
                               System.Reflection.MethodInfo.GetCurrentMethod().Name));

        }
        [Test]
        public void Invoice() 
        {
            //SimpleReportsDataSet_InvoiceReportDataTable

            //SELECT     [Order].ID, [Order].CustomerId, [Order].ShipName, [Order].ShipAddress, [Order].ShipCity, 
            //                  [Order].ShipRegion, [Order].ShipPostalCode, [Order].ShipCountry, [Order].OrderDate, [Order].RequiredDate, 
            //                  [Order].ShippedDate, OrderDetail.UnitPrice, 
            //                  OrderDetail.Quantity, OrderDetail.Discount,
            //                  CONVERT(money, (OrderDetail.UnitPrice * OrderDetail.Quantity) * (1 - OrderDetail.Discount) / 100 * 100) AS ExtendedPrice, 
            //                  [Order].Freight, OrderDetail.ProductID, [Order].ShipperId, [Order].EmployeeID, 
            //                  OrderDetail.OrderID, Employee.FirstName + N' ' + Employee.LastName AS SalesPerson, Customer.ContactName, 
            //                  Customer.Address, Customer.City, Customer.Region, Customer.PostalCode, Customer.Country, Product.Name AS ProductName, 
            //                  Shipper.Name AS ShipVia
            //FROM              [Order] INNER JOIN
            //                  OrderDetail ON [Order].ID = OrderDetail.OrderID INNER JOIN
            //                  Customer ON [Order].CustomerId = Customer.ID INNER JOIN
            //                  Employee ON [Order].EmployeeID = Employee.ID INNER JOIN
            //                  Product ON OrderDetail.ProductID = Product.ID INNER JOIN
            //                  Shipper ON [Order].ShipperId = Shipper.ID
            //WHERE     (@OrderID IS NULL) OR
            //                  (@OrderID = [Order].ID)

            int? orderId = null;

            NorthwindNETEntities _context = Globals.CreateObjectContext();
            var reportDataSet = from o in _context.Order 
                                     join d in _context.OrderDetail on o.ID equals d.OrderID
                                     join p in _context.Product on d.ProductID equals p.ID
                                     join c in _context.Customer on o.CustomerId equals c.ID
                                     join e in _context.Employee on o.EmployeeID equals e.ID
                                     join s in _context.Shipper on o.ShipperId equals s.ID 
                                where (orderId == null || o.ID == orderId)
                                select new
                                {
                                    o.ID, o.CustomerId, o.ShipName, o.ShipAddress, o.ShipCity, 
                                    o.ShipRegion, o.ShipPostalCode, o.ShipCountry, o.OrderDate, o.RequiredDate, 
                                    o.ShippedDate, d.UnitPrice, 
                                    d.Quantity, d.Discount,
                                    ExtendedPrice = (float?)d.UnitPrice * d.Quantity * (1 - d.Discount), 
                                    o.Freight,
                                    d.ProductID,
                                    o.ShipperId,
                                    o.EmployeeID, 
                                    d.OrderID,  
                                    SalesPerson = e.FirstName + " " + e.LastName, 
                                    c.ContactName, c.Address, c.City, c.Region, c.PostalCode, c.Country,  
                                    ProductName = p.Name, 
                                    ShipVia = s.Name
                                };
            foreach (var c in reportDataSet)
            {
                //System.Console.WriteLine("{0} {1} {2} {3} {4} {5} {6} {7}",
                System.Console.WriteLine("{0} {1} {2} {3} {4} {5} {6} {7}",
                                    c.ID,
                                    c.CustomerId,
                                    c.ProductName,
                                    c.Quantity,
                                    c.ExtendedPrice,
                                    c.SalesPerson,
                                    c.ContactName,
                                    c.ShipVia);
            }

            bool ret = true;
            Assert.IsTrue(ret, string.Format(Northwind.NET.Settings.Resources.TestFailedMessage,
                               System.Reflection.MethodInfo.GetCurrentMethod().Name));

        }

        [Test]
        public void CustomerCountries()
        {
            List<string> list = new List<string>();
  
            NorthwindNETEntities _context = Globals.CreateObjectContext();
            var reportDataSet = from c in _context.Customer  
                          group c by c.Country into g
                          select new
                          {
                              CountryName = g.Key
                          };

            foreach (var c in reportDataSet)
            {
                list.Add(c.CountryName);
                System.Console.WriteLine("{0}", c.CountryName);
            }

            System.Console.WriteLine(list.Count);    

            bool ret = true;
            Assert.IsTrue(ret, string.Format(Northwind.NET.Settings.Resources.TestFailedMessage, 
                               System.Reflection.MethodInfo.GetCurrentMethod().Name));
        }

        [Test]
        public void CustomersLabelsReport()
        {

            //SELECT        ID, Code, Name, Address, City, Region, PostalCode, Country
            //FROM            Customer
            //WHERE        (Country = @countryName) OR
            //             (@countryName IS NULL)

            string countryName = "USA"; // "Canada"; // "Sweden"; // null;
            NorthwindNETEntities _context = Globals.CreateObjectContext();
            var reportDataSet = from c in _context.Customer
                                where string.IsNullOrEmpty(countryName) || 
                                      string.Compare(c.Country, countryName, true) == 0
                                select new
                                {
                                    c.ID,
                                    c.Code,
                                    c.Name,
                                    c.Address,
                                    c.City,
                                    c.Region,
                                    c.PostalCode,
                                    c.Country
                                };

            foreach (var c in reportDataSet)
            {
                //System.Console.WriteLine("{0} {1} {2} {3} {4} {5} {6} {7}",
                System.Console.WriteLine("{0} {1} {2} {3} {4} {5} {6} {7}",
                                    c.ID,
                                    c.Code,
                                    c.Name,
                                    c.Address,
                                    c.City,
                                    c.Region,
                                    c.PostalCode,
                                    c.Country);
            }

            bool ret = true;
            Assert.IsTrue(ret, string.Format(Northwind.NET.Settings.Resources.TestFailedMessage, System.Reflection.MethodInfo.GetCurrentMethod().Name));
        }
        [Test]
        public void ProductByCategoryReport()
        {
            // SELECT SortGroupLetter, ProductName, CategoryName, QuantityPerUnit, UnitsInStock 
            // FROM dbo.vw_AlphabeticalListOfProductsReport
            NorthwindNETEntities _context = Globals.CreateObjectContext();
            var reportDataSet = from c in _context.Category
                                join p in _context.Product
                                on c.ID equals p.CategoryID
                                select new
                                {
                                    SortGroupLetter = p.Name.Substring(0, 1),
                                    ProductName = p.Name,
                                    CategoryName = c.Name,
                                    p.QuantityPerUnit,
                                    p.UnitsInStock
                                };

            foreach (var r in reportDataSet)
            {
                //System.Console.WriteLine("{0} {1} {2} {3} {4} {5} {6} {7}",
                System.Console.WriteLine("{0} {1} {2} {3} {4}",
                    r.SortGroupLetter, r.ProductName, r.CategoryName, r.QuantityPerUnit, r.UnitsInStock);
            }

            bool ret = true;
            Assert.IsTrue(ret, string.Format(Northwind.NET.Settings.Resources.TestFailedMessage, System.Reflection.MethodInfo.GetCurrentMethod().Name));
        }


        [Test]
        public void ShippersReport1()
        {
            NorthwindNETEntities _context = Globals.CreateObjectContext();
            var reportDataSet =  Northwind.NET.BLL.Reports.Shippers();

            foreach (var r in reportDataSet)
            {
                //Test 'Northwind.NET.Testing.Console.TestSet_0002_Reports.ShippersReport1' failed: 
                //    Microsoft.CSharp.RuntimeBinder.RuntimeBinderException : 'object' does not contain a definition for 'ShipperID'
                //    at CallSite.Target(Closure , CallSite , Object )
                //    at System.Dynamic.UpdateDelegates.UpdateAndExecute1[T0,TRet](CallSite site, T0 arg0)
                //    TestSet_0002_Reports.cs(33,0): at Northwind.NET.Testing.Console.TestSet_0002_Reports.ShippersReport1()

                //System.Console.WriteLine("{0} {1} {2} {3} {4} {5} {6} {7}",
                System.Console.WriteLine("{0} {1} {2}",
                    r.ShipperID, r.ShipperName, r.Phone); //, r.RowTimeStamp);
            }

            bool ret = true;
            Assert.IsTrue(ret, string.Format(Northwind.NET.Settings.Resources.TestFailedMessage, System.Reflection.MethodInfo.GetCurrentMethod().Name));
        }

        //ShippersReport
        //SELECT ID, Name, Phone, RowTimeStamp FROM dbo.Shipper
        [Test]
        public void ShippersReport()
        {
            NorthwindNETEntities _context = Globals.CreateObjectContext();
            var reportDataSet = from s in _context.Shipper
                                select new
                                {
                                    s.ID,
                                    s.Name,
                                    s.Phone,
                                    s.RowTimeStamp
                                };

            foreach (var r in reportDataSet)
            {
                //System.Console.WriteLine("{0} {1} {2} {3} {4} {5} {6} {7}",
                System.Console.WriteLine("{0} {1} {2} {3}",
                    r.ID, r.Name, r.Phone, r.RowTimeStamp);
            }

            bool ret = true;
            Assert.IsTrue(ret, string.Format(Northwind.NET.Settings.Resources.TestFailedMessage, System.Reflection.MethodInfo.GetCurrentMethod().Name));
        }

        //dbo.sp_CatalogReport
        [Test]
        public void CatalogReport()
        {
            NorthwindNETEntities _context = Globals.CreateObjectContext();

            //SELECT Category.ID AS CategoryId, Category.Name AS CategoryName, 
            // Category.Description, Category.Picture, Product.ID AS ProductId,
            // Product.Name AS ProductName, Product.QuantityPerUnit, Product.UnitPrice
            //FROM Category, Product
            //WHERE Category.ID = Product.CategoryId;
            var catalogReport = from c in _context.Category
                                join p in _context.Product
                                on c.ID equals p.CategoryID
                                select new
                                {
                                    CategoryId = c.ID,
                                    CategoryName = c.Name,
                                    c.Description,
                                    c.Picture,
                                    ProductId = p.ID,
                                    ProductName = p.Name,
                                    p.QuantityPerUnit,
                                    p.UnitPrice
                                };

            foreach (var r in catalogReport)
            {
                System.Console.WriteLine("{0} {1} {2} [{3}] {4} {5} {6} {7}",
                    r.CategoryId, r.CategoryName, r.Description, r.Picture.LongLength,
                    r.ProductId, r.ProductName, r.QuantityPerUnit, r.UnitPrice);
            }

            bool ret = true;
            Assert.IsTrue(ret, string.Format(Northwind.NET.Settings.Resources.TestFailedMessage, System.Reflection.MethodInfo.GetCurrentMethod().Name));
        }

        [Test]
        public void AlphabeticalListOfProductsReport2()
        {

            foreach (var r in Northwind.NET.BLL.Reports.AlphabeticalListOfProducts())
            {
                System.Console.WriteLine("{0} {1} {2} {3} {4}",
                    r.SortGroupLetter, r.ProductName, r.CategoryName, r.QuantityPerUnit, r.UnitsInStock);
            }

            bool ret = true;
            Assert.IsTrue(ret, string.Format(Northwind.NET.Settings.Resources.TestFailedMessage, System.Reflection.MethodInfo.GetCurrentMethod().Name));
        }

        //rpt_AlphabeticalListOfProductsReport
        [Test]
        public void AlphabeticalListOfProductsReport1()
        {
            NorthwindNETEntities _context = Globals.CreateObjectContext();

            //vw_AlphabeticalListOfProductsReport
            //SELECT Mid(p.Name,1,1) AS SortGroupLetter, p.Name AS ProductName, c.Name AS CategoryName, p.QuantityPerUnit, p.UnitsInStock
            //FROM Category AS c, Product AS p
            //WHERE c.ID=p.CategoryId
            var vw_AlphabeticalListOfProductsReport = 
                          from c in _context.Category
                          join p in _context.Product
                          on c.ID equals p.CategoryID 
                          select new
                          {
                              SortGroupLetter = p.Name.Substring(0,1), 
                              ProductName = p.Name , 
                              CategoryName = c.Name, 
                              p.QuantityPerUnit, 
                              p.UnitsInStock                              
                          };

            //rpt_AlphabeticalListOfProductsReport
            //SELECT SortGroupLetter, ProductName, CategoryName, QuantityPerUnit, UnitsInStock
            //FROM vw_AlphabeticalListOfProductsReport;
            var rpt_AlphabeticalListOfProductsReport =
                  from v in vw_AlphabeticalListOfProductsReport
                  select v;

            foreach (var r in rpt_AlphabeticalListOfProductsReport)
            {
                System.Console.WriteLine("{0} {1} {2} {3} {4}",
                    r.SortGroupLetter, r.ProductName, r.CategoryName, r.QuantityPerUnit, r.UnitsInStock);  
            }

            bool ret = true;
            Assert.IsTrue(ret, string.Format(Northwind.NET.Settings.Resources.TestFailedMessage, System.Reflection.MethodInfo.GetCurrentMethod().Name));
        }
    }
}